package server

import (
	"database/sql"
	"errors"
)

//获取单个家族信息
func api_get_family_info(tx *sql.Tx, person_id int64, family_id int64) ([]map[string]interface{}, error) {
	//数据校验没问题，调用查询语句进行查询
	rows, err := tx.Query(`
select a.family, ifnull(a.wife, 0) as wife, ifnull(a.husband, 0) as husband, a.id as child, ifnull(wn.nick, ifnull(w.name, '')) as wife_name, ifnull(hn.nick, ifnull(h.name, '')) as husband_name, ifnull(b.parent, 0) as parent, ifnull(b.child_male, 1) as child_male, a.priority, ifnull(wf.add_person_id, 0) as wife_creator, ifnull(hf.add_person_id, 0) as husband_creator 
	from tb_couple a 
	left join tb_parents b on b.family = ? and a.id = b.child 
	left join tb_person w on a.wife = w.id 
	left join tb_family_member wf on wf.family_id = ? and w.id = wf.person_id 
	left join tb_nicks wn on wn.family = ? and wn.main_person = ? and wn.call_person = w.id
	left join tb_person h on a.husband = h.id 
	left join tb_family_member hf on hf.family_id = ? and h.id = hf.person_id 
  left join tb_nicks hn on hn.family = ? and hn.main_person = ? and hn.call_person = h.id	
	where a.family = ?
 	`,
		family_id, family_id, family_id, person_id, family_id, family_id, person_id, family_id)
	if err != nil {
		return nil, PrefixError("查询家族信息失败", err)
	}
	result, err := SelectResultFromRows(rows)
	if err != nil {
		return nil, PrefixError("解析家族信息失败", err)
	}
	return result, nil
}

//获取某个人的家族列表
func api_get_family_list(tx *sql.Tx, person_id int64) ([]map[string]interface{}, error) {
	//查询所属的所有家族，按权重从大到小排列。
	rows, err := tx.Query("select a.id as family_id, a.name as family_name from tb_family a join tb_family_member b on a.id = b.family_id where b.person_id = ? order by a.id", person_id)
	if err != nil {
		return nil, PrefixError("查询家族列表失败", err)
	}
	result, err := SelectResultFromRows(rows)
	if err != nil {
		return nil, PrefixError("解析家族列表失败", err)
	}
	return result, nil
}

//获取默认的第一个家族, 返回家族id
func api_get_first_family(tx *sql.Tx, person_id int64) (int64, error) {
	var family_id int64
	err := tx.QueryRow("select a.id from tb_family a join tb_family_member b on a.id = b.family_id where b.person_id = ? order by a.id limit 1", person_id).Scan(&family_id)
	if err != nil {
		return 0, PrefixError("查询第一个家族失败", err)
	}
	return family_id, nil
}

//获取家族所有成员
func api_get_family_members(tx *sql.Tx, person_id int64, family_id int64) ([]map[string]interface{}, error) {
	rows, err := tx.Query(`
	select a.person_id, b.name as person_name, b.sex as person_sex, ifnull(c.nick, '') as person_nick from tb_family_member a 
	join tb_person b on a.person_id = b.id 
	left join tb_nicks c on c.family = ? and c.main_person = ? and a.person_id = c.call_person 
	where a.family_id = ?
	`, family_id, person_id, family_id)

	if err != nil {
		return nil, PrefixError("查询家族成员失败", err)
	}
	result, err := SelectResultFromRows(rows)
	if err != nil {
		return nil, PrefixError("解析家族成员失败", err)
	}
	return result, nil
}

//获取couple_id可选择的兄弟姊妹排行， 包含couple_id自己的排行
func api_get_can_select_priorities(tx *sql.Tx, couple_id int64) ([]int, error) {
	var family_id int64
	var parentCoupleId int64
	err := tx.QueryRow("select a.family, ifnull(b.parent, 0) as parentCoupleId from tb_couple a left join tb_parents b on a.family = b.family and a.id = b.child where a.id = ?", couple_id).Scan(&family_id, &parentCoupleId)
	if err != nil {
		if err.Error() == sql.ErrNoRows.Error() {
			return nil, errors.New("查询可选排行-人员不属于家族")
		} else {
			return nil, PrefixError("查询可选排行异常", err)
		}
	}
	if 0 == parentCoupleId {
		//不存在长辈，也就没有兄弟姊妹(如果有兄弟姊妹，会存在一个空长辈)
		arr := make([]int, 0)
		for i := 1; i <= 20; i++ {
			arr = append(arr, i)
		}
		return arr, nil
	} else {
		//存在长辈，查询其他兄弟姊妹的排行
		rows, err := tx.Query("select a.id as coupleId, a.priority from tb_couple a join tb_parents b on a.family = b.family and a.id = b.child where b.family = ? and b.parent = ?", family_id, parentCoupleId)
		if err != nil {
			return nil, PrefixError("查询其他兄弟姊妹排行异常", err)
		}
		data, _ := SelectResultFromRows(rows)
		brother_priorities := make([]int, 0) //兄弟姊妹已存在的排行
		for i := 0; i < len(data); i++ {
			couple, _ := data[i]["coupleId"].(int64)
			priority, _ := data[i]["priority"].(int64)
			if couple > 0 && couple != couple_id {
				brother_priorities = append(brother_priorities, int(priority))
			}
		}

		arr := make([]int, 0)
		for i := 1; i <= 20; i++ {
			if !ArrContainInt(brother_priorities, i) {
				arr = append(arr, i)
			}
		}
		return arr, nil
	}
}

//获取父母添加子女时可选择的兄弟姊妹排行
func api_get_child_can_select_priorities(tx *sql.Tx, family_id int64, parentCoupleId int64) ([]int, error) {
	//先校验数据
	var count int64
	tx.QueryRow("select count(*) from tb_couple where family = ? and id = ?", family_id, parentCoupleId).Scan(&count)
	if count != 1 {
		return nil, errors.New("查找子女排行信息-参数异常")
	}

	//查找兄弟姊妹已存在的排行
	rows, err := tx.Query("select a.priority from tb_couple a join tb_parents b on a.family = b.family and a.id = b.child where b.family = ? and b.parent = ?", family_id, parentCoupleId)
	if err != nil {
		return nil, PrefixError("查询子女排行信息异常", err)
	}
	data, _ := SelectResultFromRows(rows)
	brother_priorities, _ := GetIntColumnFromResult(data, "priority")

	arr := make([]int, 0)
	for i := int64(1); i <= 20; i++ {
		if !ArrContainInt64(brother_priorities, i) {
			arr = append(arr, int(i))
		}
	}
	return arr, nil
}
